The present invention generally relates to databases, and, more particularly, is concerned with reconciling records maintained in two separate databases or in separate tables within the same database.
Records maintained in two separate databases or in separate tables within the same database may contain related information. For example, a provider of a web-based telephone directory listing service may have developed a first database that includes records containing information relating to orders placed by customers for listings in the web-based telephone directory service. The first database might include records having data fields for customer names, addresses, phone numbers, and other information about the customer""s advertisement order. For example, the data in the fields of a particular record in the first database may identify xe2x80x9cCompany Xxe2x80x9d as having an address of xe2x80x9c1700 Lincoln Street, Denver Colo.,xe2x80x9d and a phone number of xe2x80x9c3038617000.xe2x80x9d The provider might also have developed a second database that includes records relating to the features of ordered advertisements in the web-based telephone directory listings. The second database might include records having data fields for customer names, addresses, and phone numbers as well as the type of feature ordered, such as a link to the customer""s home page or an information page about the customer, and start and end dates for the advertisement. For example, the data in the fields of a particular record in the second database may indicate that customer xe2x80x9cCompany Xxe2x80x9d located at xe2x80x9c1700 Lincoln Streetxe2x80x9d in xe2x80x9cDenver, Colo.xe2x80x9d has ordered an advertisement that is to start on xe2x80x9c10/30/96xe2x80x9d and end on xe2x80x9c10/30/97xe2x80x9d and the advertisement includes a link to the customer""s home web page. Since databases such as described above typically have numerous records, verifying that the data within a record within the first database is consistent with the data in a related record within the second databases may be a difficult and time consuming process.
The inventors of the present invention have recognized that verification of data contained within related, but separately maintained records may be facilitated by providing a method and system for identifying matching records and ensuring that the identified matching records are made consistent (i.e. reconciled).
Accordingly, one object of the present invention is to provide a method and system that facilitates the identification of matching records maintained in separate databases or in separate tables within the same database, such as, for example, matching records maintained in a first database relating to orders for web-based telephone directory listings with records maintained in a second database relating to the features of the ordered listings.
Another object of the present invention is to provide a method and system that permits identified matching records to be easily reconciled by including the same data in the matching records, such as, for example, data comprising a unique identification number.
These and other objectives and advantages are achieved by the method and system of the present invention. The present invention discloses a method and system for use in reconciling a first record, which may be selected from a plurality of first records, with a matching record included in a plurality of second records. The first records and second records may be maintained separately, such as in separate databases or in separate tables within the same database. The disclosed method and system facilitates identification of matching records by searching the second records for records that are possible matches for the first record. The possible matching records, if any, found in the plurality of second records may then be displayed together with the first record. This permits the suggested matching records to be easily compared with the first record, and if a matching record is identified, data from a predetermined field of the identified matching record may be copied to a predetermined field of the first record to reconcile the first record with its matching record from the plurality of second records.
In one aspect of the present invention, a method for use in reconciling database records includes the step of identifying at least one first record from a plurality of first records to reconcile with a plurality of second records. The first records include data within a plurality of first data fields and the second records include data within a plurality of second data fields. The method also includes the steps of identifying a predetermined number of query fields comprised of first data fields and identifying the same number of search fields comprised of second data fields. Each search field identified corresponds with a separate query field. A search of the second records for records that are possible matches for the first record is then performed. In the search, data within progressively smaller sets of the query fields is compared to data within corresponding progressively smaller sets of the search fields. For each corresponding query and search field set, the searching step generates a set of suggested matching records comprised of the second records wherein the data within each search field matches the data within each corresponding query field. For example, data within first, second, third, and fourth query field sets comprised of M, N, O, and P query fields, respectively, may be compared to data within first, second, third, and fourth search field sets comprised of M, N, O, and P search fields, respectively, wherein M represents a positive integer (e.g. 4), N represents a positive integer less than M (e.g. 3), O represents a positive integer less than N (e.g. 2), and P represents a positive integer less than O (e.g. 1). It should be appreciated that the possibility exists that a set of suggested matching records may be an empty set if no second records are found having data within the particular search field set that matches data within the corresponding query field set. It should also be noted that the query and search field sets used need not be progressively smaller by the same number of elements.
In another embodiment, the method of the present invention includes the step of displaying the first record and the sets of suggested matching records together on a first interface wherein the records within the sets of suggested matching records are reviewable to determine whether one of the records in the sets of suggested matching records matches the first record. Displaying the first record and the sets of suggested matching records together facilitates comparison of the suggested matching records with the first record. In this regard, the number of data fields for which data is displayed for each suggested matching record may be less than the total number of data fields of the second records thereby further easing comparison of the suggested matching records with the first record. As to review of the suggested matching records, a suggested matching record may be determined to be a match for the first record when, for example, all of the displayed data of the suggested matching record is the same as the data displayed for the first record. It should be appreciated that a record included in a set of suggested matching records generated by comparing data within larger query and search field sets may more likely be a match for the first record than a record included in a set of suggested records generated by comparing data within smaller query and search field sets because such a record has already been found to have matching data within more data fields. Thus, it may be advantageous to review records within sets of suggested matching records generated by comparing data within larger query and search field sets prior to those within sets generated by comparing data within smaller query and search field sets.
In a further embodiment, the method of the present invention includes the step of receiving an input from a user identifying a record from the sets of suggested matching records that matches the first record. The first record and its identified matching record from the second records may then be reconciled by copying data from a predetermined data field of the identified matching record to a predetermined data field of the first record. In the event that no matching record is found in the sets of suggested matching records, an input from the user indicating such may be received. Upon receipt of an input indicating that no matching record was found in the sets of suggested matching records, a second interface may be displayed wherein the user may execute a manual search for records in the second database that match the first record using at least one user specified query term.
According to an additional aspect of the present invention, one embodiment of a database reconciliation system includes a first record having data within a first plurality of data fields, a plurality of second records having data within a second plurality of data fields, a predetermined number of query fields comprised of first data fields, a predetermined number of search fields comprised of second data fields, and a processing apparatus capable of accessing the first record and the second records. The first record may be one of a plurality of first records. Furthermore, the first records may comprise a first database and the second records may comprise a second database, or the first records and the second records may comprise separate tables within the first database. The number of search fields is the same as the number of query fields and each search field corresponds with a separate query field. The processing apparatus may, for example, be a computer. The first record and the second records may be accessible to the processing apparatus locally or through a data network, such as, for example, a local area network.
The processing apparatus provides for searching of the second records for records that are possible matches for the first record by comparing data within progressively smaller sets of query fields to data within corresponding progressively smaller sets of search fields. For each corresponding set of query and search fields, the search generates a set of suggested matching records comprised of the second records having data within the search fields matching data within the query fields. For example, the processing apparatus may provide for searching of the second records for records that are possible matches for the first record by comparing data within first, second, third, and fourth query field sets to data within first, second, third, and fourth search field sets. The first, second, third, and fourth query field sets may be comprised of M, N, O, and P query fields, respectively, and the first, second, third, and fourth search field sets may be comprised of M, N, O, and P search fields, respectively, wherein M represents a positive integer (e.g. 4), N represents a positive integer less than M (e.g. 3), O represents a positive integer less than N (e.g. 2), and P represents a positive integer less than O (e.g. 1).
In another embodiment of the system of the present invention, the processing apparatus also provides for display of the selected record and the sets of suggested matching records together on a first interface. This permits a user of the system to easily compare the suggested matching records with the first record in order to determine whether one of the suggested matching records matches the first record. In this regard, the processing apparatus may provide for display of data within a number of predetermined data fields for each suggested matching record that is less than the total number of data fields in the suggested matching record.
In a further embodiment, the processing apparatus further provides for receipt of an input from a user of the system identifying a record from the sets of suggested matching records that matches the first record, and copying of data from a predetermined data field of the identified record to a predetermined data field of the first record. As may be appreciated, with data from the predetermined data field of the identified matching record copied into the predetermined data field of the first record, the first record is reconciled with its matching record in the plurality of second records.
In an additional embodiment of the system of the present invention, the processing apparatus provides for receipt of an input from a user of the system indicating that the user has not found a record within the sets of suggested matching records that matches the first record. Upon receipt of an input indicating that no matching record has been found, the processing apparatus may provide for execution of a manual search for records in the second plurality of records that match the first record using at least one query term input by the user.
These and other aspects and advantages of the present invention should become apparent from a review of the following detailed description when taken in conjunction with the accompanying figures.